package com.avilyne.database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.avilyne.rest.model.Book;
import com.avilyne.rest.model.Page;
import com.avilyne.rest.model.Store;

public class BookManage extends DbBean{
	// INSERT
	public static String TBL_STORE_INSERT = "INSERT INTO `android_book`.`store` (`name`, `image`, `desc`) VALUES ('%s', '%s', '%s');";
	public static String TBL_BOOK_INSERT = "INSERT INTO `android_book`.`book` (`store_id`, `title`, `author`, `image`) VALUES ('%s', '%s', '%s', '%s');";
	public static String TBL_PAGE_INSERT = "INSERT INTO `android_book`.`page` (`book_id`, `page`, `title`, `content`) VALUES ('%s', '%s', '%s', '%s');";
	
	public static int DESC_TEXT=60;
	
	public BookManage(){
		connectiondb();
	}
	
	public String getContent(int pageId,int page){
		String content = "";
		String sql = "SELECT content FROM android_book.page WHERE page.id = %s;";
		sql = String.format(sql, pageId+"");
		connectiondb();
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				content = rs.getString("content");
			}
			rs.close();
			stmt.close();
			con.close();
			return content;
		} catch (SQLException e) {
			System.out.println(e.toString());
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public Store getStore(int storeId,int page,int limit){
		Store result = new Store();
		String sql = "SELECT * FROM android_book.store WHERE store.id = %s;";
		sql = String.format(sql, storeId+"");
		connectiondb();
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.setId(storeId);
				result.setName(rs.getString("name"));
				result.setImage(rs.getString("image"));
				result.setDesc(rs.getString("desc"));
			}
			rs.close();
			stmt.close();
			con.close();
			return result;
		} catch (SQLException e) {
			System.out.println(e.toString());
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public ArrayList<Store> getListStore(int page,int limit){
		ArrayList<Store> result = new ArrayList<>();
//		// SELECT COUNT(Book_ID) AS n_copies FROM book;
		String sql = "SELECT * FROM android_book.store LIMIT %s;";
		sql = String.format(sql, limit+"");
		connectiondb();
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String image = rs.getString("image");
				String desc = rs.getString("desc");
				Store store = new Store(id, name, image, new ArrayList<Book>());
				store.setDesc(desc);
				result.add(store);
			}
			rs.close();
			stmt.close();
			con.close();
			return result;
		} catch (SQLException e) {
			System.out.println(e.toString());
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public ArrayList<Book> getListBook(int storeId, int page){
		ArrayList<Book> result = new ArrayList<>();
		String sql = "SELECT * FROM android_book.book where store_id = %s;";
		sql = String.format(sql, storeId+"");
		connectiondb();
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				int id = rs.getInt("id");
				String title = rs.getString("title");
				String author = rs.getString("author");
				String image = rs.getString("image");
				Book book = new Book();
				book.id = id;
				book.title = title;
				book.image = image;
				book.author = author;
				book.storeId = storeId;
				result.add(book);
			}
			rs.close();
			stmt.close();
			con.close();
			return result;
		} catch (SQLException e) {
			System.out.println(e.toString());
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public ArrayList<Book> getBanner(int storeId, int page){
		ArrayList<Book> result = new ArrayList<>();
		String sql = "SELECT * FROM android_book.book GROUP BY store_id;";
		sql = String.format(sql, storeId+"");
		connectiondb();
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				int id = rs.getInt("id");
				String title = rs.getString("title");
				String author = rs.getString("author");
				String image = rs.getString("image");
				Book book = new Book();
				book.id = id;
				book.title = title;
				book.image = image;
				book.author = author;
				book.storeId = storeId;
				result.add(book);
			}
			rs.close();
			stmt.close();
			con.close();
			return result;
		} catch (SQLException e) {
			System.out.println(e.toString());
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public ArrayList<Page> getListChap(int bookId, int page){
		ArrayList<Page> result = new ArrayList<>();
		String sql = "SELECT * FROM android_book.page where book_id = %s ORDER BY page;";
		sql = String.format(sql, bookId+"");
		connectiondb();
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				int _id = rs.getInt("id");
				int _page = rs.getInt("page");
				String _title = rs.getString("title");
				String _content = "";
				try{
					_content = rs.getString("content").substring(0, DESC_TEXT);
				}catch(Exception e){
					_content = rs.getString("content");
				}
				Page item = new Page(_id, bookId, _page, _title, _content);
				result.add(item);
			}
			rs.close();
			stmt.close();
			con.close();
			return result;
		} catch (SQLException e) {
			System.out.println(e.toString());
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
}
